﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess.Operate
{
    public class DA_Project_Search_Inherit:SqlInteractionBase
    {
        public DataTable getResult(string Project_Name, string Project_SignTypeID,string Project_SignDateBegin,string Project_SignDateEnd, string Project_Developer, string Project_DeveloperProvinceID, string Project_DeveloperCityID, string Project_DeveloperDistrictID, string Project_DeveloperAddress, string Project_PresentID,string Project_Type,string Agency_Type,string Kd,string Building)
        {
            string strSql = "select Project_ID,Project_NO,Project_SignDate,Project_DepartmentID,Project_Name,Project_SignTypeID,Project_PresentID,(select Project_SignType_Name from t_Dic_Project_SignType where Project_SignType_ID=Project_SignTypeID) as Project_SignTypeName,((select AuditType_Name from dbo.t_Dic_AuditType where AuditType_ID=Project_Audit)+'已审')as Project_Audit,";
            strSql += "((select Province_Name from t_Dic_Project_Province where Province_ID=Project_DeveloperProvinceID)  +(select City_Name from t_Dic_Project_City where City_ID=Project_DeveloperCityID)   +(select District_Name from t_Dic_Project_District where District_ID=Project_DeveloperDistrictID)  +Project_DeveloperAddress) as Project_Detail_ProjectAddress";
            strSql += " from t_Project  where 1=1 ";

            if (Project_Name != "") { strSql += "and Project_Name like '%" + Project_Name + "%' "; }
            if (Project_SignTypeID != "") { strSql += "and Project_SignTypeID=" + Convert.ToInt32(Project_SignTypeID) + " "; }
            if (Project_SignDateBegin != "" && Project_SignDateEnd != "") { strSql += "and Project_SignDate between '" + Convert.ToDateTime(Project_SignDateBegin) + "' and '" + Convert.ToDateTime(Project_SignDateEnd)+"'"; }
            //if (Project_AgencyDateBegin != "" && Project_AgencyDateEnd != "") { strSql += "and dbo.IsUpdated(Project_Main_ID,'" + Convert.ToDateTime(Project_AgencyDateBegin) + "','" + Convert.ToDateTime(Project_AgencyDateEnd) + "')>0 "; }
            if (Project_Developer != "") { strSql += "and Project_Developer like '%" + Project_Developer + "%' "; }
            if (Project_DeveloperProvinceID != "") { strSql += "and Project_DeveloperProvinceID=" + Convert.ToInt32(Project_DeveloperProvinceID) + " "; }
            if (Project_DeveloperCityID != "") { strSql += "and Project_DeveloperCityID=" + Convert.ToInt32(Project_DeveloperCityID) + " "; }
            if (Project_DeveloperDistrictID != "") { strSql += "and Project_DeveloperDistrictID=" + Convert.ToInt32(Project_DeveloperDistrictID) + " "; }
            if (Project_DeveloperAddress != "") { strSql += "and Project_DeveloperAddress like '%" + Project_DeveloperAddress + "%' "; }
            if (Project_PresentID != "") { strSql += "and Project_PresentID=" + Convert.ToInt32(Project_PresentID) + " "; }
            if (Kd != "") { strSql += "and Project_KDNameID='"+Kd+"'"; }
            //if (Project_Type != "") { strSql += "and Project_Type in(" + Project_Type + ") "; }
            //if (Agency_Type != "") { strSql += "and Agency_Type in(" + Agency_Type + ") "; }

            if (Building != "")
            {
                strSql += " and Project_ID in(SELECT Project_Hr_MainID   FROM [DB_EcSalseManage].[dbo].[t_Project_Hr] where Project_Hr_KDNameID in (" + Building + "))";
            }
            SqlCommand cmdToExecute = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
            DataSet ds = new DataSet();

            cmdToExecute.CommandText = strSql;
            cmdToExecute.CommandType = CommandType.Text;

            try
            {
                cmdToExecute.Connection = _mainConnection;

                if (_mainConnectionIsCreatedLocal)
                {
                    _mainConnection.Open();
                }
                else
                {
                    if (_mainConnectionProvider.IsTransactionPending)
                    {
                        cmdToExecute.Transaction = _mainConnectionProvider.CurrentTransaction;
                    }
                }

                adapter.Fill(ds);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                if (_mainConnectionIsCreatedLocal)
                {
                    _mainConnection.Close();
                }

                cmdToExecute.Dispose();

                adapter.Dispose();
            }

            return ds.Tables[0];
        }

    }
}
